Imports System.Data.SqlClient

Public Class Paciente : Inherits Persona
    Private Estado As Estado
    Private IdTipoPaciente As TipoPaciente
    Private IdObraSocial As ObraSocial
    Private NroAfiliado As String
    Private NombrePlan As String
    Private IdEstado As Integer
    Private IdObraSocPac As Integer
    Private idTP As Integer
    Private idTipoD As Integer ' tipo de documento del paciente
    Private observa As String

    Private IdTipoTel As Integer
    Private NroTel As String
    Public Property ObsPac() As String
        Get
            Return observa
        End Get
        Set(ByVal value As String)
            observa = value
        End Set
    End Property
    Public Property TipoTel() As Integer
        Get
            Return IdTipoTel
        End Get
        Set(ByVal value As Integer)
            IdTipoTel = value
        End Set
    End Property
    Public Property NroTelefono() As String
        Get
            Return NroTel
        End Get
        Set(ByVal value As String)
            NroTel = value
        End Set
    End Property
    Public Property idDoc() As Integer
        Get
            Return idTipoD
        End Get
        Set(ByVal value As Integer)
            idTipoD = value
        End Set
    End Property
    Public Property idTipoPac() As Integer
        Get
            Return idTP
        End Get
        Set(ByVal value As Integer)
            idTP = value
        End Set
    End Property

    Public Property IDOSOCpac() As Integer
        Get
            Return IdObraSocPac
        End Get
        Set(ByVal value As Integer)
            IdObraSocPac = value
        End Set
    End Property
    Public Property IdEstPac() As Integer
        Get
            Return IdEstado
        End Get
        Set(ByVal value As Integer)
            IdEstado = value
        End Set
    End Property
    Public Property NomPlanPac() As String
        Get
            Return NombrePlan
        End Get
        Set(ByVal value As String)
            NombrePlan = value
        End Set
    End Property
    Public Property ObraSocialP() As ObraSocial
        Get
            Return IdObraSocial
        End Get
        Set(ByVal value As ObraSocial)
            IdObraSocial = value
        End Set
    End Property
    Public Property NroAfiliadoP() As String
        Get
            Return NroAfiliado
        End Get
        Set(ByVal value As String)
            NroAfiliado = value
        End Set
    End Property
    Public Property TipoPaciente() As TipoPaciente
        Get
            Return IdTipoPaciente
        End Get
        Set(ByVal value As TipoPaciente)
            IdTipoPaciente = value
        End Set
    End Property
    Public Property EstadoPac() As Estado
        Get
            Return Estado
        End Get
        Set(ByVal value As Estado)
            Estado = value
        End Set
    End Property
    Public Sub New()
        MyBase.New()
        Estado = Nothing
        IdTipoPaciente = Nothing
        IdObraSocial = Nothing
        NroAfiliado = ""
        NombrePlan = ""
        IdTipoTel = 0
        NroTel = ""
        observa = ""
    End Sub
    Public Overrides Function BuscarDatos(ByRef con As ConexionBD) As System.Data.DataSet
        Dim sql As String = ""
        Dim adapter As SqlDataAdapter = Nothing
        Dim datos As DataSet = New DataSet()

        sql = "SELECT * FROM PACIENTE ORDER BY apellido"
        adapter = New SqlDataAdapter(sql, con.ConexionBase)
        adapter.Fill(datos, "paciente")

        Return datos

    End Function
    Public Function BuscarPaciente(ByVal tipoDoc As Integer, ByVal numdoc As String, ByRef con As ConexionBD) As Boolean

        Dim comando As SqlCommand
        Dim rs As SqlDataReader
        Dim sql As String
        Dim lResp As Boolean = False

        sql = String.Format("SELECT *  FROM paciente WHERE idtipodoc ={0}and nrodoc ='{1}'", tipoDoc, numdoc)
        comando = New SqlCommand(sql, con.ConexionBase)
        rs = comando.ExecuteReader()
        comando.Dispose()

        If rs.Read Then
            MyBase.tipodocumentoPer = rs("idtipodoc")
            MyBase.NroDocumentoPer = rs("nrodoc")
            MyBase.NombrePer = rs("nombre")
            MyBase.ApellidoPer = rs("apellido")
            MyBase.CallePer = rs("calle")
            MyBase.NroCallePer = rs("Nro")
            MyBase.DptoPer = rs("Dpto")
            MyBase.PisoPer = rs("Piso")

            Dim ObjBarr As Barrio = New Barrio()
            ObjBarr.Buscar(rs("IdBarrio"), con)
            MyBase.Barrio = ObjBarr

            Dim ObjTipoPac As TipoPaciente = New TipoPaciente()
            ObjTipoPac.Buscar(rs("IdtipoPaciente"), con)
            TipoPaciente = ObjTipoPac

            MyBase.EmailPer = rs("Email")

            Dim ObjObraSoc As ObraSocial = New ObraSocial()
            ObjObraSoc.Buscar(rs("idObraSocial"), con)
            ObraSocialP = ObjObraSoc

            Me.NomPlanPac = rs("nomPlan")
            Me.NroAfiliadoP = rs("nroafiliado")

            Dim ObjEstado As Estado = New Estado()
            ObjEstado.Buscar(rs("IdEstado"), con)
            EstadoPac = ObjEstado
            MyBase.FecNacPer = rs("FechaNac")

            Me.ObsPac = rs("Observ")

            lResp = True
        Else
            lResp = False

        End If
        rs.Close()
        Return lResp

    End Function
    Public Overrides Function Buscar(ByVal tipoDoc As Integer, ByVal numDoc As String, ByRef con As ConexionBD) As Boolean
        Dim comando As SqlDataAdapter
        Dim rs As DataTableReader
        Dim dato As DataTable
        Dim sql As String
        Dim lResp As Boolean = False

        sql = String.Format("SELECT *  FROM paciente WHERE idtipodoc ={0}and nrodoc ='{1}'", tipoDoc, numDoc)
        comando = New SqlDataAdapter(sql, con.ConexionBase)
        dato = New DataTable("paciente")
        comando.Fill(dato)
        rs = dato.CreateDataReader()
        comando.Dispose()
        dato.Dispose()

        If rs.Read Then

            MyBase.tipodocumentoPer = rs("idtipodoc")
            MyBase.NroDocumentoPer = rs("nrodoc")
            MyBase.NombrePer = rs("nombre")
            MyBase.ApellidoPer = rs("apellido")
            MyBase.CallePer = rs("calle")
            MyBase.NroCallePer = rs("Nro")
            MyBase.DptoPer = rs("Dpto")
            MyBase.PisoPer = rs("Piso")

            Dim ObjBarr As Barrio = New Barrio()
            ObjBarr.Buscar(rs("IdBarrio"), con)
            MyBase.Barrio = ObjBarr

            Dim ObjTipoPac As TipoPaciente = New TipoPaciente()
            ObjTipoPac.Buscar(rs("IdtipoPaciente"), con)
            TipoPaciente = ObjTipoPac

            MyBase.EmailPer = rs("Email")

            Dim ObjObraSoc As ObraSocial = New ObraSocial()
            ObjObraSoc.Buscar(rs("idObraSocial"), con)
            ObraSocialP = ObjObraSoc

            Me.NomPlanPac = rs("nomPlan")
            Me.NroAfiliadoP = rs("nroafiliado")

            Dim ObjEstado As Estado = New Estado()
            ObjEstado.Buscar(rs("IdEstado"), con)
            EstadoPac = ObjEstado
            MyBase.FecNacPer = rs("FechaNac")
            ObsPac = rs("Observ")

            lResp = True
        Else
            lResp = False
        End If
        rs.Close()
        Return lResp
    End Function
    Public Function BuscarAN(ByVal ape As String, ByVal nom As String, ByRef con As ConexionBD) As Boolean
        Dim comando As SqlCommand
        Dim rs As SqlDataReader
        Dim sql As String
        Dim lResp As Boolean = False

        sql = String.Format("SELECT *  FROM paciente WHERE apellido ='{0}' and nombre ='{1}'", ape, nom)
        comando = New SqlCommand(sql, con.ConexionBase)
        rs = comando.ExecuteReader()
        comando.Dispose()

        If rs.Read Then

            MyBase.tipodocumentoPer = rs("idtipodoc")
            MyBase.NroDocumentoPer = rs("nrodoc")
            lResp = True
        Else
            lResp = False
        End If
        rs.Close()
        Return lResp
    End Function
    Public Function validarPK(ByVal idTipoDoc As Integer, ByVal numDoc As Integer, ByRef con As ConexionBD) As Integer
        Dim SQL As String = ""
        Dim comando As SqlCommand
        sql = "SELECT COUNT(*) FROM Paciente WHERE idtipodoc={0} AND nrodoc='{1}'"
        sql = String.Format(sql, Me.idTipoD, Me.NroDocumentoPer)
        comando = New SqlCommand(sql, con.ConexionBase)

        Dim count As Integer
        count = comando.ExecuteScalar()
        Return count
    End Function
    Public Function Agregar(ByRef con As ConexionBD) As Boolean
        Dim comando As SqlCommand = New SqlCommand()
        Dim sql As String = ""

        sql = "INSERT INTO Paciente (IdTipoDoc,NroDoc,Apellido,Nombre,Calle,Nro,Piso,Dpto,IdBarrio,Email,IdEstado,IdTipoPaciente,IdObraSocial,nomPlan,NroAfiliado,FechaNac,Observ)" + _
                      "  VALUES({0},'{1}','{2}','{3}','{4}',{5},'{6}','{7}',{8},'{9}',{10},{11},{12},'{13}','{14}','{15}','{16}')"

        sql = String.Format(sql, Me.idDoc, Me.NroDocumentoPer, Me.ApellidoPer, Me.NombrePer, Me.CallePer, Me.NroCallePer, Me.PisoPer, Me.DptoPer, Me.IdBarrioPer, Me.EmailPer, Me.IdEstPac, Me.idTipoPac, Me.IDOSOCpac, Me.NomPlanPac, Me.NroAfiliadoP, Format(Me.FecNacPer, "dd/MM/yyyy"), Me.ObsPac)

        comando = New SqlCommand(sql, con.ConexionBase)
        If comando.ExecuteNonQuery() > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
    Public Function AgregarTel(ByRef con As ConexionBD) As Boolean
        Dim comando As SqlCommand = New SqlCommand()
        Dim sql As String = ""

        sql = "INSERT INTO TelefonoXPersona (IdTipoDoc,NroDoc,IdTipoTel,NroTel)" + _
                      "  VALUES({0},'{1}',{2},'{3}')"

        sql = String.Format(sql, Me.idDoc, Me.NroDocumentoPer, Me.TipoTel, Me.NroTel)

        comando = New SqlCommand(sql, con.ConexionBase)
        If comando.ExecuteNonQuery() > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
    Public Function Telefonos(ByRef con As ConexionBD) As DataSet
        Dim datos As DataSet = BuscarTelefonos(con)
        Return datos
    End Function
    Public Function Eliminar(ByVal id As Integer, ByVal numdoc As String, ByRef con As ConexionBD) As Boolean

        Dim sql As String = ""
        Dim comando As SqlCommand = Nothing

        sql = "DELETE FROM PACIENTE WHERE idtipodoc ={0} and nrodoc='{1}'"
        sql = String.Format(sql, id, numdoc)
        comando = New SqlCommand(sql, con.ConexionBase)
        If comando.ExecuteNonQuery() > 0 Then
            Return True
        Else
            Return False
        End If

    End Function
    Public Function EliminarTelefonos(ByVal id As Integer, ByVal numdoc As String, ByVal idtipotel As Integer, ByVal nrotel As String, ByRef con As ConexionBD) As Boolean

        Dim sql As String = ""
        Dim comando As SqlCommand = Nothing

        sql = "DELETE FROM TELEFONOXPERSONA WHERE idtipodoc ={0} and nrodoc='{1}' and idtipotel={2} and nrotel='{3}'"
        sql = String.Format(sql, id, numdoc, idtipotel, nrotel)
        comando = New SqlCommand(sql, con.ConexionBase)
        If comando.ExecuteNonQuery() > 0 Then
            Return True
        Else
            Return False
        End If

    End Function
    Public Function Modificar(ByVal id As Integer, ByVal numdoc As String, ByRef con As ConexionBD) As Boolean
        Dim sql As String = ""
        Dim comando As SqlCommand = Nothing

        sql = "UPDATE PACIENTE " + _
              "  SET APELLIDO = '{2}'," + _
              "      NOMBRE ='{3}'," + _
              "      CALLE ='{4}'," + _
              "      NRO ={5}," + _
              "      DPTO ='{6}'," + _
              "      PISO ='{7}'," + _
              "      IDBARRIO ={8}," + _
              "      EMAIL ='{9}'," + _
              "      IDESTADO ={10}," + _
              "      IDTIPOPACIENTE ={11}," + _
              "      IDOBRASOCIAL ={12}," + _
              "      NOMPLAN ='{13}'," + _
              "      NROAFILIADO ='{14}'," + _
              "      FECHANAC ='{15}'," + _
              "      OBSERV='{16}'" + _
              "      WHERE IDTIPODOC = {0} AND NRODOC='{1}'"
        sql = String.Format(sql, Me.idDoc, Me.NroDocumentoPer, Me.ApellidoPer, Me.NombrePer, Me.CallePer, Me.NroCallePer, Me.PisoPer, Me.DptoPer, Me.IdBarrioPer, Me.EmailPer, Me.IdEstPac, Me.idTipoPac, Me.IDOSOCpac, Me.NomPlanPac, Me.NroAfiliadoP, Format(Me.FecNacPer, "dd/MM/yyyy"), Me.ObsPac)
        comando = New SqlCommand(sql, con.ConexionBase)
        If comando.ExecuteNonQuery() > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

End Class